package org.simplecrm.repository.customer;

import org.simplecrm.domain.customer.Customer;
import org.simplecrm.domain.event.Event;
import org.simplecrm.domain.event.EventStatus;
import org.simplecrm.domain.event.eventtype.EventType;
import org.simplecrm.domain.region.Region;
import org.simplecrm.repository.event.EventDAO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;

/**
 * Implementation for JDBC database.
 */
@Repository("customerDAO")
//@EnableTransactionManagement
//@Transactional
public class CustomerJdbcDAO implements CustomerDAO {

    private static final Logger LOGGER = LoggerFactory.getLogger(CustomerJdbcDAO.class);

    private static final String SELECT_WHERE_ID = "SELECT * FROM Customer WHERE id=?";
    private static final String SELECT_ALL = "SELECT * FROM Customer";
    private static final String SELECT_EVENTS_OF_CUSTOMER = "SELECT * FROM Event WHERE customerId=?";
    private static final String SELECT_REGION_WHERE_ID = "SELECT * FROM Region WHERE id=?";

    private DataSource ds;

    @Autowired
    public CustomerJdbcDAO(DataSource ds) {
        this.ds = ds;
    }

    @Override
    public List<Customer> getAllCustomers() {

        List<Customer> customers = new LinkedList<>();

        try (Connection con = ds.getConnection();
             PreparedStatement statement = con.prepareStatement(SELECT_ALL,
                     ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_UPDATABLE);
             ResultSet resultset = statement.executeQuery()) {

            while (resultset.next()) {

                Customer customer = null;
                Region region = null;
                List<Event> eventCollection = null;

                int customerId = resultset.getInt(CUST_ID_FIELD);
                String name = resultset.getString(CUST_NAME_FIELD);
                float totalIncome = resultset.getFloat(CUST_INCOME_FIELD);
                int regionId = resultset.getInt(CUST_REGIONID_FIELD);

                region = getRegion(con, regionId);
                eventCollection = getEvents(con, customerId);

                customer = new Customer(customerId, name, region, totalIncome, eventCollection);
                customers.add(customer);
            }
        } catch (SQLException ex) {
            LOGGER.error(ex.toString(), ex);
            customers = null;
        }

        return customers;
    }

    @Override
    public void deleteCustomer(int id) {

        try (Connection con = ds.getConnection();
             PreparedStatement statement = con.prepareStatement(SELECT_WHERE_ID,
                     ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_UPDATABLE)) {

            statement.setInt(1, id);

            try (ResultSet resultset = statement.executeQuery()) {
                while (resultset.next()) {
                    resultset.deleteRow();
                }
            }

        } catch (SQLException ex) {
            LOGGER.error(ex.toString(), ex);
        }

    }

    @Override
    public Customer getCustomerById(int id) {

        Customer customer = null;
        Region region = null;

        try (Connection con = ds.getConnection();
             PreparedStatement statement = con.prepareStatement(SELECT_WHERE_ID,
                     ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_UPDATABLE)) {

            statement.setInt(1, id);

            try (ResultSet resultset = statement.executeQuery()) {

                while (resultset.next()) {

                    List<Event> eventCollection = null;

                    String name = resultset.getString(CUST_NAME_FIELD);
                    float totalIncome = resultset.getFloat(CUST_INCOME_FIELD);
                    int regionId = resultset.getInt(CUST_REGIONID_FIELD);

                    region = getRegion(con, regionId);
                    eventCollection = getEvents(con, id);

                    customer = new Customer(id, name, region, totalIncome, eventCollection);
                }
            }

        } catch (SQLException ex) {
            LOGGER.error(ex.toString(), ex);
        }

        return customer;
    }

    @Override
    //@Transactional
    public void addNewCustomer(Customer newCustomer) {

        try (Connection con = ds.getConnection();
             PreparedStatement statement = con.prepareStatement(SELECT_ALL,
                     ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_UPDATABLE);
             ResultSet resultset = statement.executeQuery()) {

            resultset.moveToInsertRow();

            resultset.updateString(CUST_NAME_FIELD, newCustomer.getName());
            resultset.updateInt(CUST_REGIONID_FIELD, newCustomer.getRegion().getId());
            resultset.updateFloat(CUST_INCOME_FIELD, newCustomer.getTotalIncome());

            resultset.insertRow();

            //throw new SQLException("TEST EXPECTION!");

        } catch (SQLException ex) {
            LOGGER.error(ex.toString(), ex);
        }
    }

    @Override
    public void updateCustomer(Customer changedCustomer) {

        try (Connection con = ds.getConnection();
             PreparedStatement statement = con.prepareStatement(SELECT_WHERE_ID,
                     ResultSet.TYPE_SCROLL_INSENSITIVE,
                     ResultSet.CONCUR_UPDATABLE)) {

            statement.setInt(1, changedCustomer.getId());

            try (ResultSet resultset = statement.executeQuery();) {

                while (resultset.next()) {

                    resultset.updateString(CUST_NAME_FIELD, changedCustomer.getName());
                    resultset.updateInt(CUST_REGIONID_FIELD, changedCustomer.getRegion().getId());
                    resultset.updateFloat(CUST_INCOME_FIELD, changedCustomer.getTotalIncome());

                    resultset.updateRow();
                }
            }

        } catch (SQLException ex) {
            LOGGER.error(ex.toString(), ex);
        }
    }

    private static List<Event> getEvents(Connection con, int customerId) throws SQLException {

        List<Event> eventCollection;
        eventCollection = new LinkedList<>();
        try (PreparedStatement eventsStatement = con.prepareStatement(SELECT_EVENTS_OF_CUSTOMER);) {
            eventsStatement.setInt(1, customerId);
            try (ResultSet eventsResultset = eventsStatement.executeQuery()) {

                while (eventsResultset.next()) {
                    Event event = null;

                    int id = eventsResultset.getInt(EventDAO.EVENT_ID_FIELD);

                    int eventTypeId = eventsResultset.getInt(EventDAO.EVENT_TYPEID_FIELD);

                    EventType type = null;
                    String eventTypeName;
                    try (PreparedStatement eventTypeStatement = con.prepareStatement(
                            "SELECT * FROM EventType WHERE id=?");) {
                        eventTypeStatement.setInt(1, eventTypeId);
                        try (ResultSet eventTypeResultset = eventTypeStatement.executeQuery()) {
                            eventTypeName = eventTypeResultset.getString("name");//TODO:hardcode
                        }
                    }
                    type = new EventType(eventTypeId, eventTypeName);

                    String desc = eventsResultset.getString(EventDAO.EVENT_DESCRIPTION_FIELD);
                    Date creationDate = eventsResultset.getDate(EventDAO.EVENT_DATE_FIELD);
                    EventStatus status = EventStatus.valueOf(eventsResultset.getString(EventDAO.EVENT_STATUS_FIELD));
                    int supportId = eventsResultset.getInt(EventDAO.EVENT_SUPPORT_ID_FIELD);

                    event = new Event(id, type, desc, creationDate,
                            status, customerId, supportId);
                    eventCollection.add(event);
                }
            }
        }
        return eventCollection;
    }

    private static Region getRegion(Connection con, int regionId) throws SQLException {

        String regionName;
        try (PreparedStatement regionStatement = con.prepareStatement(SELECT_REGION_WHERE_ID);) {
            regionStatement.setInt(1, regionId);

            try (ResultSet regionResultset = regionStatement.executeQuery()) {
                regionResultset.next();
                regionName = regionResultset.getString("name");//TODO:hardcode
            }
        }

        return new Region(regionId, regionName);
    }
}
